﻿using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class AcQuyDao
    {
        public DataTable LoadAcQuy()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("Load_Acquy", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

        public DataTable Load_LoaiAcQuy()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("Load_LoaiAcquy", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }

        public List<LoaiAcQuy> LoadLoaiCQuy_Them1dong()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("Load_LoaiAcquy", _cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader rd = cmd.ExecuteReader();
            List<LoaiAcQuy> list = new List<LoaiAcQuy>();
            LoaiAcQuy acQuy;
            while (rd.Read())
            {
                acQuy = new LoaiAcQuy();
                acQuy.MaLoaiAcQuy = rd.GetInt32(0);
                acQuy.TenLoai = rd.GetString(1);
                list.Add(acQuy);
            }
            return list;
        }

        public DataTable Loai_AcQuy()
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlDataAdapter _da = new SqlDataAdapter("Loai_AcQuy", _cn);
            _da.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataTable _dt = new DataTable();
            _da.Fill(_dt);
            return _dt;
        }



        public int Them_AcQuy(ThongSoChungDTO ts, ThietBiDienDTO tb)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("THEM_AcQuy", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char, 20));
            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@NgayNhap", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@CSKhaDung", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DonGia", SqlDbType.Int));

            cmd.Parameters["@MaTB"].Value = ts.MATB;
            cmd.Parameters["@TenTB"].Value = ts.TENTB;
            cmd.Parameters["@NgayNhap"].Value = ts.NGAYNHAP;
            cmd.Parameters["@CSKhaDung"].Value = ts.CSKHADUNG;
            cmd.Parameters["@DonGia"].Value = ts.DONGIA;

            cmd.Parameters.Add(new SqlParameter("@Vol", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@Amp", SqlDbType.Decimal));

            
            cmd.Parameters.Add(new SqlParameter("@LoaiTB", SqlDbType.Int));

            cmd.Parameters["@Vol"].Value = tb.VOL;
            cmd.Parameters["@Amp"].Value = tb.AMP;
            cmd.Parameters["@LoaiTB"].Value = tb.LOAITB;

            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }


        public int Sua_AcQuy(ThongSoChungDTO ts, ThietBiDienDTO tb)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("Sua_AcQuy", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@STT", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char, 20));
            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@NgayNhap", SqlDbType.DateTime));
            cmd.Parameters.Add(new SqlParameter("@CSKhaDung", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@DonGia", SqlDbType.Int));

            cmd.Parameters["@STT"].Value = tb.STT;
            cmd.Parameters["@MaTB"].Value = ts.MATB;
            cmd.Parameters["@TenTB"].Value = ts.TENTB;
            cmd.Parameters["@NgayNhap"].Value = ts.NGAYNHAP;
            cmd.Parameters["@CSKhaDung"].Value = ts.CSKHADUNG;
            cmd.Parameters["@DonGia"].Value = ts.DONGIA;

            cmd.Parameters.Add(new SqlParameter("@Vol", SqlDbType.Float));
            cmd.Parameters.Add(new SqlParameter("@Amp", SqlDbType.Decimal));

            cmd.Parameters.Add(new SqlParameter("@LoaiTB", SqlDbType.Int));

            cmd.Parameters["@Vol"].Value = tb.VOL;
            cmd.Parameters["@Amp"].Value = tb.AMP;
            cmd.Parameters["@LoaiTB"].Value = tb.LOAITB;

            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }

        public int Xoa_AcQuy(int STT)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("Xoa_AcQuy", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@STT", SqlDbType.Int));
            cmd.Parameters["@STT"].Value = STT;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }

        public int KiemTra_MaAQ(ThongSoChungDTO ts)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("MaAQ_KT", _cn);
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add(new SqlParameter("@MaTB", SqlDbType.Char, 20));
            cmd.Parameters.Add(new SqlParameter("@TonTai", SqlDbType.Int));
            cmd.Parameters["@TonTai"].Direction = ParameterDirection.Output;


            cmd.Parameters["@MaTB"].Value = ts.MATB;
            cmd.Parameters["@TonTai"].Value = ts.MATS;

            cmd.ExecuteNonQuery();
            int _kq = (int)cmd.Parameters["@TonTai"].Value;
            _cn.Close();
            return _kq;
        }
        public int KT_TheoTenAcQuy(ThongSoChungDTO ts)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("KT_TheoTenAcQuy", _cn);
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add(new SqlParameter("@TenTB", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@TonTai", SqlDbType.Int));
            cmd.Parameters["@TonTai"].Direction = ParameterDirection.Output;


            cmd.Parameters["@TenTB"].Value = ts.TENTB;
           // cmd.Parameters["@TonTai"].Value = ts.MATS;

            cmd.ExecuteNonQuery();
            int _kq = (int)cmd.Parameters["@TonTai"].Value;
            _cn.Close();
            return _kq;
        }


        public int Them_LoaiAcQuy(LoaiAcQuy AQ)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("Them_LoaiAcQuy", _cn);
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@ma", SqlDbType.Int));
            cmd.Parameters["@ma"].Direction = ParameterDirection.Output;


            cmd.Parameters["@TenLoai"].Value = AQ.TenLoai;
            cmd.Parameters["@ma"].Value = AQ.MaLoaiAcQuy;

            cmd.ExecuteNonQuery();
            int _kq = (int)cmd.Parameters["@ma"].Value;
            _cn.Close();
            return _kq;
        }
        //-----------------------------LOAI----------------------------------

        public int Sua_LoaiAcQuy(LoaiAcQuy aq)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("Sua_LoaiAcQuy", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));
            cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 50));

            cmd.Parameters["@MaLoai"].Value = aq.MaLoaiAcQuy;
            cmd.Parameters["@TenLoai"].Value = aq.TenLoai;
           
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }


        public int KiemTra_TenAcQuy(LoaiAcQuy aq)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("TenAcQuy_KT", _cn);
            cmd.CommandType = CommandType.StoredProcedure;


            cmd.Parameters.Add(new SqlParameter("@TenLoai", SqlDbType.NVarChar, 50));
            cmd.Parameters.Add(new SqlParameter("@TonTai", SqlDbType.Int));
            cmd.Parameters["@TonTai"].Direction = ParameterDirection.Output;


            cmd.Parameters["@TenLoai"].Value = aq.TenLoai;
            //cmd.Parameters["@TonTai"].Value = 

            cmd.ExecuteNonQuery();
            int _kq = (int)cmd.Parameters["@TonTai"].Value;
            _cn.Close();
            return _kq;
        }


        //-----------------------Xoa LOai---------------------------------

        public int Xoa_LoaiAcQuy(LoaiAcQuy lAQ)
        {
            SqlConnection _cn = Provider.ConnectDB();
            SqlCommand cmd = new SqlCommand("Xoa_LoaiACQuy", _cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@MaLoai", SqlDbType.Int));
            cmd.Parameters["@MaLoai"].Value = lAQ.MaLoaiAcQuy;
            int _kq = cmd.ExecuteNonQuery();
            _cn.Close();
            return _kq;
        }

    }
}
